package com.wxh.excel;

import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @ClassName: ExcelUtils
 * @Description: exceL工具类
 * @Author wxh
 * @Date: 2019/11/5 10:18
 * @Version V1.0.0
 * @Since 1.8
 */
public class ExcelUtils {

	public static SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");

	public static final String OFFICE_EXCEL_2003 = "xls";
	public static final String OFFICE_EXCEL_2007 = "xlsx";

	private ExcelUtils(){

	}

	/**
	 * 获取一个工作表
	 * @param workBook
	 * @param ch_list
	 * @param en_list
	 * @param dataList
	 * @param sheetIndex
	 * @param sheetName
	 */
	public static void createXSSFSheet(XSSFWorkbook workBook,List<String> ch_list, List<String> en_list, List<Map<String, Object>> dataList,
									   int sheetIndex, String sheetName){
		XSSFSheet xssfSheet = workBook.createSheet();
//		workBook.setSheetName(0, sheetName);
		workBook.setSheetName(sheetIndex, sheetName);

		// 设置常规样式
		XSSFCellStyle generalStyle = ExcelStyle.getGeneralStyle(workBook);
		generalStyle.setLocked(false);
		// 内容不可编辑
		XSSFCellStyle lockedStyle = ExcelStyle.getGeneralStyle(workBook);
		lockedStyle.setLocked(true);
		lockedStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		lockedStyle.setFillForegroundColor(IndexedColors.RED.getIndex());

		// 列头不可编辑
		XSSFCellStyle lockedTitleStyle = ExcelStyle.getGeneralStyle(workBook);
		lockedTitleStyle.setLocked(true);
		lockedTitleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		lockedTitleStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

		// 创建一行
		XSSFRow zero_row = xssfSheet.createRow(0);
		//设置行高
		zero_row.setHeight((short) 580);
		// 设置表格标题
		for(int i = 0; i < ch_list.size(); i++) {
			XSSFCell cell = zero_row.createCell(i);
			cell.setCellValue(ch_list.get(i));
			cell.setCellStyle(lockedTitleStyle);
		}
		// 填充表格标题下单元格数据
		for (int i = 0; i < dataList.size(); i++) {
			Map<String, Object> entryMap = dataList.get(i);
			XSSFRow row = xssfSheet.createRow(1 + i);

			//设置行高
			row.setHeight((short) 320);

			for (int j = 0; j < en_list.size(); j++) {
				XSSFCell cell = row.createCell(j);
				// 默认是锁定状态；将所有单元格设置为：未锁定；然后再对需要上锁的单元格单独锁定
				cell.setCellStyle(generalStyle);
				// 第一列和第二列设置不可编辑
				if(j == 0 || j== 1 || j== 2){
					cell.setCellStyle(lockedStyle);
					cell.setCellValue(MyStringUtils.valueOf(entryMap.get(en_list.get(j))));
				}else{
					cell.setCellValue(MyStringUtils.valueOf(entryMap.get(en_list.get(j))));
				}
			}

		}
		// sheet添加保护，这个一定要否则光锁定还是可以编辑的
		xssfSheet.protectSheet("123456");
	}

	/**
	 * 自己创建一个xlsx文件,并将数据写入xlsx中
	 * @param workBook 工作簿
	 * @param fileName 文件名称
	 * @param resp    响应对象
	 * @param excelPath  文件路径
	 * @throws IOException
	 */
	public static void exportExcelByNew2(XSSFWorkbook workBook, String fileName, HttpServletResponse resp, String excelPath) throws Exception {
		File dir = new File(excelPath);
		if(!dir.exists()){
			dir.mkdirs();
		}
		String fullFilePath = excelPath + fileName + ".xlsx";
		File file = new File(fullFilePath);
		if(!file.exists()){
			file.createNewFile();
		}

		FileOutputStream fos = new FileOutputStream(file);
		workBook.write(fos);
		fos.close();
		System.out.println("----导出成功----");
	}

	/**
	 * 自己创建一个xlsx文件,并将数据写入xlsx中
	 * @param fileName 文件名称
	 * @param ch_list 表格标题中文名集合
	 * @param en_list 表格标题英文名集合
	 * @param dataList 数据
	 * @param resp    响应对象
	 * @throws IOException
	 */
	public static void exportExcelByNew(String fileName, List<String> ch_list, List<String> en_list, List<Map<String, Object>> dataList, HttpServletResponse resp) throws Exception {
		
		// 读取excel文件进行填空
		XSSFWorkbook workBook = new XSSFWorkbook();
		XSSFSheet xssfSheet = workBook.createSheet();
		workBook.setSheetName(0, fileName);

		// 设置常规样式
		XSSFCellStyle generalStyle = ExcelStyle.getGeneralStyle(workBook);

        // 设置列宽
        // xssfSheet.setColumnWidth(0, 200);
		XSSFRow zero_row = xssfSheet.createRow(0);
		//设置行高
		zero_row.setHeight((short) 580);
		// 设置表格标题
		for(int i = 0; i < ch_list.size(); i++) {
			XSSFCell cell = zero_row.createCell(i);
			cell.setCellValue(ch_list.get(i));
			cell.setCellStyle(generalStyle);
		}
		// 填充表格标题下单元格数据
		for (int i = 0; i < dataList.size(); i++) {
			Map<String, Object> entryMap = dataList.get(i);
			XSSFRow row = xssfSheet.createRow(1 + i);

			// 设置行高
			row.setHeight((short) 320);

			for (int j = 0; j < en_list.size(); j++) {
				XSSFCell cell = row.createCell(j);
				cell.setCellValue(MyStringUtils.valueOf(entryMap.get(en_list.get(j))));
				cell.setCellStyle(generalStyle);
			}

		}
		// 以附件方式写入输出流中，让浏览器自动下载
		resp.addHeader("Content-disposition",
				"attachment;filename=" + new String((fileName).getBytes("GBK"), "ISO8859_1") + ".xlsx");
		OutputStream webOut = resp.getOutputStream();
		workBook.write(webOut);
		webOut.close();
		System.out.println("----导出成功----");

	}


	/**
	 * 根据模板来创建一个xlsx文件,并将数据写入xlsx中
	 * @param fileName 文件名
	 * @param pathName 模板文件路径
	 * @param dataList 数据
	 * @param index 数据从第几行写入索引
	 * @param resp 响应对象
	 * @throws IOException
	 */
	public void exportExcelByTemplate(String fileName, String pathName, List<Map<String, Object>> dataList, int index, HttpServletResponse resp) throws IOException {
		// 找到模板文件地址
		ClassLoader classLoader = this.getClass().getClassLoader();
		// 因为是中文名称获取时时乱码需要decode转换
		InputStream is = classLoader.getResourceAsStream(pathName);
		// 读取excel文件进行填空
		XSSFWorkbook workBook = new XSSFWorkbook(is);
		XSSFSheet xssfSheet = workBook.getSheetAt(0);

		// 设置常规样式
		XSSFCellStyle generalStyle = ExcelStyle.getGeneralStyle(workBook);

		// 获取列名：英文名
		XSSFRow columnRow = xssfSheet.getRow(index - 1);

		List<String> columnList = new ArrayList<String>();
		for (int i = 0; i < columnRow.getPhysicalNumberOfCells(); i++) {
			columnList.add(MyStringUtils.valueOf(columnRow.getCell(i)));
		}

		for (int i = 0; i < dataList.size(); i++) {
			Map<String, Object> entryMap = dataList.get(i);
			XSSFRow row = xssfSheet.createRow(index - 1 + i);

			// 设置行高
			row.setHeight((short) 320);

			for (int j = 0; j < columnList.size(); j++) {
				XSSFCell cell = row.createCell(j);
				cell.setCellValue(MyStringUtils.valueOf(entryMap.get(columnList.get(j))));
				cell.setCellStyle(generalStyle);
			}

		}

		resp.addHeader("Content-disposition",
				"attachment;filename=" + new String((fileName).getBytes("GBK"), "ISO8859_1") + ".xlsx");
		OutputStream webOut = resp.getOutputStream();
		workBook.write(webOut);
		webOut.close();
		System.out.println("----导出成功----");

	}




}
